/**
 * 
 */
package cs342.project1.mathNotebook.db;

import java.lang.ref.WeakReference;
import java.security.PublicKey;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Vector;

/** Databased-mapped Person object.  <br><br>
 * 
 * A DBPerson is never directly instantiated.  Always call one of the static factory methods to create or get DBPersons.
 * Like in DatabaseContext, SQL statements use a combination of printf arguments and prepareStatement parameters.
 * @author Aaron Heise
 *
 */
public class DBPerson extends DBObject {
	
	/**
	 * Keeps a weak reference to all DBPersons by context for caching purposes.
	 */
	protected static Map<DatabaseContext, Map<String, WeakReference<DBPerson>>> initializedContexts = new HashMap<DatabaseContext, Map<String, WeakReference<DBPerson>>>();
	
	protected String username  = null;
	protected String fullname  = null;
	protected String email     = null;
	protected byte[] publicKey = null;
	
	/**
	 * Table name for records of instances of this object
	 */
	public static final String PERSON_TABLE_NAME = "people";
	/**
	 * Column name for username.  PRIMARY KEY for instances of this object
	 */
	public static final String PERSON_TABLECOLUMN_USERNAME = "username";
	/**
	 * Column name for full name of person.
	 */
	public static final String PERSON_TABLECOLUMN_FULLNAME = "fullname";
	/**
	 * Column name for person's email address
	 */
	public static final String PERSON_TABLECOLUMN_EMAIL    = "email";
	/**
	 * Column name for person's public key.
	 */
	public static final String PERSON_TABLECOLUMN_KEY      = "public_key";
	/**
	 * Create SQL statement for table containing instances of this object.
	 * 
	 * <br>printf arg0: table name
	 */
	protected static final String PERSON_TABLE_DDL = "CREATE TABLE IF NOT EXISTS %s (" +
														PERSON_TABLECOLUMN_USERNAME + " VARCHAR(50) NOT NULL PRIMARY KEY, " +
														PERSON_TABLECOLUMN_FULLNAME + " VARCHAR(50) NOT NULL, " +
														PERSON_TABLECOLUMN_EMAIL    + " VARCHAR(50) NOT NULL, " +
														PERSON_TABLECOLUMN_KEY      + " BLOB        NOT NULL)"; 
	/**
	 * Select SQL statement to get all instances of this object.
	 * 
	 * <br>printf arg0: DBPerson table name
	 */
	protected static final String GET_ALL_INSTANCES_SQL = "SELECT " + PERSON_TABLECOLUMN_USERNAME + ", " +
															PERSON_TABLECOLUMN_FULLNAME + ", " +
															PERSON_TABLECOLUMN_EMAIL    + ", " +
															PERSON_TABLECOLUMN_KEY      + " FROM %s";
	/**
	 * Select SQL statement to get one instance of this object.
	 * 
	 * <br>printf arg0: DBPerson table name
	 * <br>ps   param1: DBPerson username to get
	 */
	protected static final String GET_INSTANCE_SQL =  GET_ALL_INSTANCES_SQL + " WHERE " +
															PERSON_TABLECOLUMN_USERNAME + " = ?"; 
	/**
	 * Insert SQL statement to create one instance of this object.
	 * 
	 * <br>printf arg0: DBPerson table name
	 * <br>  ps param1: DBPerson username to create
	 * <br>  ps param2: DBPerson fullname
	 * <br>  ps param3: DBPerson email
	 * <br>  ps param4: DBPerson public key
	 */
	protected static final String INSERT_INSTANCE_SQL = "INSERT INTO %s (" + 
															PERSON_TABLECOLUMN_USERNAME + ", " +
															PERSON_TABLECOLUMN_FULLNAME + ", " + 
															PERSON_TABLECOLUMN_EMAIL + ", " +
															PERSON_TABLECOLUMN_KEY + ") VALUES(?,?,?,?)"; 
	
	/** Checks that context has not already been set up, and if not, creates tables if they do not exist.
	 * @param context context to set up
	 * @throws DatabaseException if cannot connect to database or if there is an SQL error
	 */
	public static void setUpContext(DatabaseContext context) throws DatabaseException {
		if(initializedContexts.get(context) == null) {
			context.executeSQLNoResults(String.format(DBPerson.PERSON_TABLE_DDL, 
														context.getPrefixedTableName(DBPerson.PERSON_TABLE_NAME)));					
			initializedContexts.put(context, new HashMap<String, WeakReference<DBPerson>>());
		}
	}

	/** Drops tables from context.
	 * @param context context to clear
	 * @throws DatabaseException if cannot connect to database or if there is an SQL error
	 */
	public static void clearContext(DatabaseContext context) throws DatabaseException {
		if(initializedContexts.remove(context) != null) {
			DBNotebook.clearContext(context);
			context.dropTable(DBPerson.PERSON_TABLE_NAME);
		}
	}
	
	/** Create an uninitialized DBPerson instance.
	 * @param context DatabaseContext to associate instance with
	 * @param username primary key of this instance
	 * @throws DatabaseException if cannot connect to database or if there is an SQL error
	 */
	protected DBPerson(DatabaseContext context, String username) throws DatabaseException {
		super(context);
		DBPerson.setUpContext(context);
		this.username = username;
		DBPerson.initializedContexts.get(context).put(username, new WeakReference<DBPerson>(this));
	}

	/* (non-Javadoc)
	 * @see cs342.project1.mathNotebook.db.DBObject#initializeFromContext()
	 */
	@Override
	protected void initializeFromContext() throws DatabaseException {
		String sql = String.format(GET_INSTANCE_SQL, context.getPrefixedTableName(PERSON_TABLE_NAME));
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(sql);
			ps.setString(1, this.username);
			ResultSet rs = ps.executeQuery();
			rs.last();
			if(rs.getRow() != 1) {
				throw new DatabaseExecutionException("Wrong number of results for object creation query");
			} else {
				this.initializeFromResultSetCurrentRow(rs);
			}
		} catch (Exception e) {
			throw new DatabaseObjectNotFoundException("Could not create object", e);
		}
	}

	/** Initialize this instance using the data in the current row of the supplied ResultSet
	 * @param rs source of data for initializing object
	 * @throws DatabaseException if cannot connect to database or if there is an SQL error
	 */
	protected void initializeFromResultSetCurrentRow(ResultSet rs) throws DatabaseException {
		try {
			this.fullname = rs.getString(PERSON_TABLECOLUMN_FULLNAME);
			this.email    = rs.getString(PERSON_TABLECOLUMN_EMAIL);
			this.publicKey = rs.getBytes(PERSON_TABLECOLUMN_KEY);
			this.initialized = true;
		} catch (SQLException e) {
			throw new DatabaseExecutionException("Error reading from result set", e);
		}		
	}

	/* (non-Javadoc)
	 * @see cs342.project1.mathNotebook.db.DBObject#writeToContext()
	 */
	@Override
	protected void writeToContext() throws DatabaseException {
		String sql = String.format(GET_INSTANCE_SQL, context.getPrefixedTableName(PERSON_TABLE_NAME));
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(sql);
			ps.setString(1,this.username);
			ResultSet rs = ps.executeQuery();
			if(rs.last()) {
				if(rs.getRow() != 1) {
					throw new DatabaseObjectNotFoundException("Wrong number of results for object query");
				}
				rs.updateString(PERSON_TABLECOLUMN_FULLNAME, this.fullname);
				rs.updateString(PERSON_TABLECOLUMN_EMAIL, this.email);
				rs.updateBytes(PERSON_TABLECOLUMN_KEY, this.publicKey);
				rs.updateRow();
				rs.close();
				ps.close();
			} else {
				ps.close();
				sql = String.format(INSERT_INSTANCE_SQL, context.getPrefixedTableName(PERSON_TABLE_NAME));
				ps = context.getConnection().prepareStatement(sql);
				ps.setString(1, this.username);
				ps.setString(2, this.fullname);
				ps.setString(3, this.email);
				ps.setBytes(4, this.publicKey);
				ps.executeUpdate();
				ps.close();
			}
			this.invalidate();
		} catch (Exception e) {
			throw new DatabaseObjectNotFoundException("Could not write object", e);
		}
	}
	
	/**
	 * @return the Username (PRIMARY KEY) of this object (does not trigger initialization)
	 * @throws DatabaseException if cannot connect to database or if there is an SQL error
	 */
	public String getUsername() throws DatabaseException {
		return username;
	}
	
	/**
	 * @return the full name of this object. (triggers initialization of object if not initialized)
	 * @throws DatabaseException if cannot connect to database or if there is an SQL error
	 */
	public String getFullname() throws DatabaseException {
		this.initializeIfNeeded();
		return fullname;
	}
	
	/**
	 * @return the email of this object (triggers initialization of object if not initialized)
	 * @throws DatabaseException if cannot connect to database or if there is an SQL error
	 */
	public String getEmail() throws DatabaseException {
		this.initializeIfNeeded();
		return email;
	}
	
	/**
	 * @return the public key of this object (trigges initialization of object if not initialized)
	 * @throws DatabaseException if cannot connect to database or if there is an SQL error
	 */
	public byte[] getPublicKey() throws DatabaseException {
		this.initializeIfNeeded();
		return publicKey;
	}

	/** Create a new DBPerson in database and return the mapped object. (INSERT)
	 * @param context context in which to create person
	 * @param username username to create (PRIMARY KEY)
	 * @param fullname name of person
	 * @param email email address of person
	 * @param publicKey public key of person
	 * @return the DBPerson created
	 * @throws DatabaseException if cannot connect to database or if there is an SQL error
	 */
	public static DBPerson createPerson(DatabaseContext context, String username, String fullname, String email, byte[] publicKey) throws DatabaseException {
		DBPerson newPerson = new DBPerson(context, username);
		newPerson.fullname = fullname;
		newPerson.email = email;
		newPerson.publicKey = publicKey;
		newPerson.writeToContext();
		return newPerson;
	}
	
	public static DBPerson getPersonByUsername(DatabaseContext context, String username) throws DatabaseException {
		DBPerson p = DBPerson.getLoadedPersonByUsername(context, username);
		return (p != null ? p : new DBPerson(context, username));
	}
	
	public static DBPerson getLoadedPersonByUsername(DatabaseContext context, String username) throws DatabaseException {
		DBPerson.setUpContext(context);
		DBPerson p = null;
		Map<String, WeakReference<DBPerson>> pMap = DBPerson.initializedContexts.get(context);
		WeakReference<DBPerson> wp = pMap.get(username);
		if(wp != null) {
			p = wp.get();
			
			//remove expired entries
			if(p == null) {
				pMap.remove(wp);
			}
		}
		return p;
	}
	
	public static DBPerson[] getAllPersons(DatabaseContext context) throws DatabaseException {
		DBPerson.setUpContext(context);
		String sql = String.format(GET_ALL_INSTANCES_SQL, context.getPrefixedTableName(PERSON_TABLE_NAME));
		DBPerson[] people = null;
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			rs.last();
			people = new DBPerson[rs.getRow()];
			rs.beforeFirst();
			for(int i = 0; rs.next(); i++) {
				String username = rs.getString(PERSON_TABLECOLUMN_USERNAME);
				DBPerson p = DBPerson.getLoadedPersonByUsername(context, username);
				
				//if p is null, then username is not loaded.  Create a new person.
				//then, since all of the data is already here, initialize the object right away.
				if(p == null) {
					p = new DBPerson(context, username);
					p.initializeFromResultSetCurrentRow(rs);
				}
				
				people[i] = p;
			}

		} catch (Exception e) {
			throw new DatabaseExecutionException("Problem enumerating objects", e);
		}
		return people;
	}
	
	public static boolean isUsernameAvailable(DatabaseContext context, String username) throws DatabaseException {
		DBPerson[] persons = getAllPersons(context);
		for(int i = 0; i < persons.length; i++) {
			if(persons[i].username.equals(username)) {
				return false;
			}
		}
		return true;
	}

}
